unit stocktake;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, FileUtil, LResources, Forms, Controls, Graphics, Dialogs,
  ExtCtrls, Grids, StdCtrls, Buttons, Spin, Sqlite3DS, IniFiles, clientdb, fpstructs;

type

  { TStocktakeForm }

  TStocktakeForm = class(TForm)
    ServerLabel: TLabel;
    OKButton: TBitBtn;
    ClearButton: TBitBtn;
    BarcodeEdit: TEdit;
    CloseButton: TBitBtn;
    FinishButton: TBitBtn;
    ItemNameLabel: TLabel;
    SaveDialog: TSaveDialog;
    TopPanel: TPanel;
    ListPanel: TPanel;
    CountSpinEdit: TSpinEdit;
    CheckedItemsStringGrid: TStringGrid;
    ButtonPanel: TPanel;
    procedure BarcodeEditChange(Sender: TObject);
    procedure CloseButtonClick(Sender: TObject);
    procedure FinishButtonClick(Sender: TObject);
    procedure FormActivate(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure OKButtonClick(Sender: TObject);
    procedure ClearButtonClick(Sender: TObject);
    procedure CountSpinEditKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    function GetBarcode( barcode: shortstring; var aProduct: TfpProduct ): integer;
  private
    { private declarations }
  public
    StocktakeFile: TFilename;
    Config: TIniFile;
    MainDB: TClientDB;
    Product: TfpProduct;
    { public declarations }
  end; 

var
  StocktakeForm: TStocktakeForm;

  SQLITEDATABASE: TFilename;
  DEFAULTDB: TFilename;


implementation

uses  stocktakestart, resolveadddlg, fposUtils;

{ TStocktakeForm }

procedure TStocktakeForm.BarcodeEditChange(Sender: TObject);
var
  inputcode: String;
begin
  inputcode := BarcodeEdit.Text;
  ItemNameLabel.Caption:='';
  OKButton.Enabled := false;
  if Length(inputcode)= 6 then
     begin
       if GetBarcode(inputcode, Product) > 0 then // try to retrieve in-house barcode
          begin
            ItemNameLabel.Caption := Product.name ;
            CountSpinEdit.Value := -1;
            CountSpinEdit.SetFocus;
            CountSpinEdit.SelectAll;
            OKButton.Enabled := true;
          end;
     end
  else
    if Length(inputcode)> 12 then
       begin
         if GetBarcode(inputcode, Product) > 0 then // try to retrieve standard barcode
            begin
             ItemNameLabel.Caption := Product.name ;
             CountSpinEdit.Value := -1;
             CountSpinEdit.SetFocus;
             CountSpinEdit.SelectAll;
             OKButton.Enabled := true;
            end;
       end;

end;

procedure TStocktakeForm.CloseButtonClick(Sender: TObject);
begin
  if MessageDlg('Close program', 'Do you really want to close the stocktaker?', mtConfirmation, mbYesNoCancel, 0)=mrYes
     then Close;
end;

procedure TStocktakeForm.FinishButtonClick(Sender: TObject);
var
  header: String;

  r: Integer;
  DB: TSqlite3Dataset;
  query: TCaption;
  outFile: TextFile;
  lin: String;
  x: Integer;

  function makeline(item, count, cost, value : string): ansistring;
  var
    lijn: ansistring;
  begin
  {  lijn := '\trowd\trql\trpaddft3\trpaddt55\trpaddfl3\trpaddl55\trpaddfb3\trpaddb55\trpaddfr3\trpaddr55\clbrdrl\brdrs\brdrw1\brdrcf1\clbrdrb\brdrs\brdrw1\brdrcf1\cellx6063\clbrdrl\brdrs\brdrw1\brdrcf1\clbrdrb\brdrs\brdrw1\brdrcf1\cellx7438\clbrdrl\brdrs\brdrw1\brdrcf1'
    + ' \clbrdrb\brdrs\brdrw1\brdrcf1\cellx8488\clbrdrl\brdrs\brdrw1\brdrcf1\clbrdrb\brdrs\brdrw1\brdrcf1\clbrdrr\brdrs\brdrw1\brdrcf1\cellx9638'
+ '\pard\intbl\pard\plain \intbl\ltrpar\s7\cf0' + #123 + '\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0' + #125 + '\rtlch\af4\afs24\lang1081\ltrch\dbch\af3\langfe2052\hich\f0\fs24\lang2057\loch\f0\fs24\lang2057' + #123 + '\rtlch \ltrch\loch\f0\fs24\lang2057\i0\b0 ' + item + #125
+ ' \cell\pard\plain \intbl\ltrpar\s7\cf0' + #123 + '\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0' + #125 + '\rtlch\af4\afs24\lang1081\ltrch\dbch\af3\langfe2052\hich\f0\fs24\lang2057\loch\f0\fs24\lang2057' + #123 + '\rtlch \ltrch\loch\f0\fs24\lang2057\i0\b0 ' + count + #125
+ '\cell\pard\plain \intbl\ltrpar\s7\cf0' + #123 + '\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0' + #125 + '\rtlch\af4\afs24\lang1081\ltrch\dbch\af3\langfe2052\hich\f0\fs24\lang2057\loch\f0\fs24\lang2057' + #123 + '\rtlch \ltrch\loch\f0\fs24\lang2057\i0\b0 ' + cost + #125
+ '\cell\pard\plain \intbl\ltrpar\s7\cf0' + #123 + '\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0' + #125 + '\rtlch\af4\afs24\lang1081\ltrch\dbch\af3\langfe2052\hich\f0\fs24\lang2057\loch\f0\fs24\lang2057' + #123 + '\rtlch \ltrch\loch\f0\fs24\lang2057\i0\b0 ' + value + #125
+ '\cell\row\pard ';                 }
  end;
begin
  if SaveDialog.Execute then
     begin
      DB := TSqlite3Dataset.Create(nil);
      DB.FileName := SQLITEDATABASE;
       try
         AssignFile(outFile, SaveDialog.FileName);
         Rewrite(outFile);
         lin := '"barcode","name","cost","count","value"';
         WriteLn(outFile, lin);
         DB.SQL := 'SELECT barcode, name, cost, count, count*cost FROM stocktake'
                                   + ' ORDER BY barcode;';
         DB.Open;
         query := '';
         for x := 1 to DB.RecordCount do
         begin
           lin :=   '"' + DB.Fields[0].AsString + '",'
                  + '"' + DB.Fields[1].AsString + '",'
                  + FormatFloat('0.00', DB.Fields[2].AsFloat) + ','
                  + DB.Fields[3].AsString + ','
                  + FormatFloat('0.00', DB.Fields[4].AsFloat);
           WriteLn(outFile, lin);
           query := query + 'UPDATE Products SET stocklevel=' + DB.FieldByName('count').AsString
                    + ' WHERE barcode="' + DB.FieldByName('barcode').AsString + '";';
           DB.Next;
         end;

//         MainDB.ExecRemoteSQL(query);
       finally
         DB.Free;
         CloseFile(outFile);
         MainDB.Close;
       end;
     end;

  { header := #123 + '\rtf1\ansi\deff0\adeflang1025'
 + #123 + '\fonttbl' + #123 + '\f0\froman\fprq2\fcharset128 Times New Roman;' + #125 + '' + #123 + '\f1\froman\fprq2\fcharset128 Times New Roman;' + #125 + '' + #123 + '\f2\fswiss\fprq2\fcharset128 Arial;' + #125 + '' + #123 + '\f3\fnil\fprq2\fcharset128 Bitstream Vera Sans;' + #125 + '' + #123 + '\f4\fnil\fprq2\fcharset128 Lohit Hindi;' + #125 + '' + #123 + '\f5\fnil\fprq0\fcharset128 Lohit Hindi;' + #125 + '' + #125
 + #123 + '\colortbl;\red0\green0\blue0;\red128\green128\blue128;' + #125
 + #123 + '\stylesheet' + #123 + '\s1\cf0' + #123 + '\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0' + #125 + '\rtlch\af4\afs24\lang1081\ltrch\dbch\af3\langfe2052\hich\f0\fs24\lang2057\loch\f0\fs24\lang2057\snext1 Normal;' + #125
 + #123 + '\s2\sb240\sa120\keepn\cf0' + #123 + '\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0' + #125 + '\rtlch\afs28\lang1081\ltrch\dbch\langfe2052\hich\f2\fs28\lang2057\loch\f2\fs28\lang2057\sbasedon1\snext3 Heading;' + #125
 + #123 + '\s3\sa120\cf0' + #123 + '\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0' + #125 + '\rtlch\af4\afs24\lang1081\ltrch\dbch\af3\langfe2052\hich\f0\fs24\lang2057\loch\f0\fs24\lang2057\sbasedon1\snext3 Body Text;' + #125
 + #123 + '\s4\sa120\cf0' + #123 + '\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0' + #125 + '\rtlch\af5\afs24\lang1081\ltrch\dbch\af3\langfe2052\hich\f0\fs24\lang2057\loch\f0\fs24\lang2057\sbasedon3\snext4 List;' + #125
 + #123 + '\s5\sb120\sa120\cf0' + #123 + '\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0' + #125 + '\rtlch\af5\afs24\lang1081\ai\ltrch\dbch\af3\langfe2052\hich\f0\fs24\lang2057\i\loch\f0\fs24\lang2057\i\sbasedon1\snext5 caption;' + #125
 + #123 + '\s6\cf0' + #123 + '\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0' + #125 + '\rtlch\af5\afs24\lang1081\ltrch\dbch\af3\langfe2052\hich\f0\fs24\lang2057\loch\f0\fs24\lang2057\sbasedon1\snext6 Index;' + #125
 + #123 + '\s7\cf0' + #123 + '\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0' + #125 + '\rtlch\af4\afs24\lang1081\ltrch\dbch\af3\langfe2052\hich\f0\fs24\lang2057\loch\f0\fs24\lang2057\sbasedon1\snext7 Table Contents;' + #125
 + #125
 + #123 + '\info' + #123 + '\creatim\yr2011\mo4\dy8\hr15\min27' + #125 + '' + #123 + '\revtim\yr0\mo0\dy0\hr0\min0' + #125 + '' + #123 + '\printim\yr0\mo0\dy0\hr0\min0' + #125 + '' + #123 + '\comment StarWriter' + #125 + '' + #123 + '\vern3200' + #125#125 + '\deftab709'
 + #123 + '\*\pgdsctbl'
 + #123 + '\pgdsc0\pgdscuse195\pgwsxn11906\pghsxn16838\marglsxn1134\margrsxn1134\margtsxn1134\margbsxn1134\pgdscnxt0 Standard;' + #125#125
 + '\paperh16838\paperw11906\margl1134\margr1134\margt1134\margb1134\sectd\sbknone\pgwsxn11906\pghsxn16838\marglsxn1134\margrsxn1134\margtsxn1134\margbsxn1134\ftnbj\ftnstart1\ftnrstcont\ftnnar\aenddoc\aftnrstcont\aftnstart1\aftnnrlc'
 + '\pard\plain \ltrpar\s1\cf0' + #123 + '\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0' + #125 + '\rtlch\af4\afs24\lang1081\ltrch\dbch\af3\langfe2052\hich\f0\fs24\lang2057\loch\f0\fs24\lang2057' + #123 + '\rtlch \ltrch\loch\f0\fs24\lang2057\i0\b0 Mallaig Heritage Centre' + #125
 + '\par \pard\plain \ltrpar\s1\cf0' + #123 + '\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0' + #125 + '\rtlch\af4\afs24\lang1081\ltrch\dbch\af3\langfe2052\hich\f0\fs24\lang2057\loch\f0\fs24\lang2057'
 + '\par \pard\plain \ltrpar\s1\cf0' + #123 + '\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0' + #125 + '\rtlch\af4\afs24\lang1081\ltrch\dbch\af3\langfe2052\hich\f0\fs24\lang2057\loch\f0\fs24\lang2057' + #123 + '\rtlch \ltrch\loch\f0\fs24\lang2057\i0\b0 Stocktake' + #125
 }
end;

procedure TStocktakeForm.FormActivate(Sender: TObject);
var
  DB: TSqlite3Dataset;
  DBOKFlag: Boolean;
  r: Integer;
  recorddate: string;
  fpserver: String;
begin
  if SQLITEDATABASE='' then
     begin
      fpserver := Config.ReadString('User', 'ServerAddr', '');
       if fpserver=''
          then fpserver := InputBox('', 'FirePOS Server address', '' );
       if fpserver<>''
               then Config.WriteString('User', 'ServerAddr', fpserver);
       ServerLabel.Caption := 'FirePOS server: ' + fpserver;
     //   fpserver := 'localhost';
       //fpserver := '80.189.248.87';
       MainDB := TClientDB.Create;
       MainDB.Server := fpserver;
        StocktakeStartForm.FileNameEdit1.Text := Config.ReadString('User', 'Stocktake', '');
        StocktakeStartForm.ContinueStocktakeOption.Checked := StocktakeStartForm.FileNameEdit1.FileName <> '';
        if StocktakeStartForm.ShowModal = mrOK then
             begin
               SQLITEDATABASE := StocktakeStartForm.FileNameEdit1.Text;
               Config.WriteString('User', 'Stocktake', StocktakeStartForm.FileNameEdit1.Text);
                DBOKFlag := false;
                r := 0;
                if FileExists(StocktakeStartForm.FileNameEdit1.Text) then  // check if it really is a stocktake db
                   begin
                    DB := TSqlite3Dataset.Create(Application);
                    try
                      DB.FileName := SQLITEDATABASE;
                      DB.SQL := 'SELECT * FROM stocktake'; //DB.OpenTable('stocktake', 'SELECT * FROM stocktake');
                      DB.Open;
                      if DB.RecordCount>0 then
                         while not DB.EOF do
                         begin
                           Inc(r);
                           recorddate := DB.FieldByName('date').AsString;
                           CheckedItemsStringGrid.RowCount := r+1;
                           CheckedItemsStringGrid.Cells[0,r] := DB.FieldByName('barcode').AsString;
                           CheckedItemsStringGrid.Cells[1,r] := DB.FieldByName('name').AsString;
                           CheckedItemsStringGrid.Cells[2,r] := Copy(recorddate,7,2) + DateSeparator
                                                                + Copy(recorddate,5,2) + DateSeparator
                                                                + Copy(recorddate,1,4);
                           CheckedItemsStringGrid.Cells[3,r] := DB.FieldByName('count').AsString;
                           DB.Next;
                         end;
                      DBOKFlag := true;
                    finally
                      DB.Free;
                     // SQLITEDATABASE := DEFAULTDB;
                    end;
                   end
                else           // create new stocktake db
                  try
                    DB := TSqlite3Dataset.Create(Application);
                    DBOKFlag := CreateStocktake(StocktakeStartForm.FileNameEdit1.Text);
                  finally
                    DB.Free;
                    //SQLITEDATABASE := DEFAULTDB;
                  end;
                if DBOKFlag then
                   begin
                    StocktakeFile := StocktakeStartForm.FileNameEdit1.Text;
                    Caption := 'FirePOS Stocktaker: ' + StocktakeStartForm.FileNameEdit1.Text;
                   end;
                //SQLITEDATABASE := DEFAULTDB;   StocktakeStartForm.FileNameEdit1.FileName := Config.ReadString('User', 'Stocktake', '');
             end
        else Close;
  end;
end;

procedure TStocktakeForm.FormCreate(Sender: TObject);
var
  fpserver: String;
begin
  if not DirectoryExists(GetAppConfigDir(false)) then ForceDirectories(GetAppConfigDir(false));
  Config := TIniFile.Create(GetAppConfigDir(false) + 'fpstocktaker.cfg');
  ItemNameLabel.Caption := '';
  OKButton.Enabled := false;
//  fpserver := '192.168.1.35';
end;

procedure TStocktakeForm.FormShow(Sender: TObject);
begin
end;

procedure TStocktakeForm.OKButtonClick(Sender: TObject);
  function ItemAlreadyInStocktake: boolean;
  var
    DB: TSqlite3Dataset;
    r: integer;
  begin
    Result := false;
    try
      SQLITEDATABASE := StocktakeFile;
      DB := TSqlite3Dataset.Create(Application);
      DB.FileName := SQLITEDATABASE;
      DB.SQL := 'SELECT name, count, date FROM stocktake WHERE barcode=''' + BarcodeEdit.Text + '''';
      DB.Open;
      if DB.RecordCount>0 then
         begin
           Result := true;
           ResolveExistingItemDlg.InformationLabel.Caption := 'Item ''' + DB.FieldByName('name').AsString + ''' has already appeared '
                 + ' in the stocktake ' + #10 + '( ' + DB.FieldByName('count').AsString + ' items '
                 + 'counted on ' + Copy(DB.FieldByName('date').AsString,7,2) + DateSeparator
                                                  + Copy(DB.FieldByName('date').AsString,5,2) + DateSeparator
                                                  + Copy(DB.FieldByName('date').AsString,1,4) + ' )';
           if ResolveExistingItemDlg.ShowModal <> mrOK                                 // if dialog is cancelled
              then ResolveExistingItemDlg.optionkeepexisting.Checked := true;          // make no changes
         end;
    finally
      DB.Free;
    end;
  end;

  procedure StocktakeNewItem;
  var
    DB: TSqlite3Dataset;
    r: integer;
   begin
     try
       SQLITEDATABASE := StocktakeFile;
       DB := TSqlite3Dataset.Create(Application);
       DB.FileName := SQLITEDATABASE;
       DB.ExecSQL( 'INSERT INTO stocktake (barcode, name, count, cost, date, servercount)'
                            + ' VALUES(''' + BarcodeEdit.Text + ''', '''
                            +  EscapeApostrophe( Product.name ) + ''','
                            +  IntToStr(CountSpinEdit.Value) + ','
                            +  FloatToStr(Product.costprice) + ','''
                            +  FormatDateTime('yyyymmdd',Date) + ''', '
                            +  IntToStr(Product.stocklevel) + ')');
       CheckedItemsStringGrid.RowCount := CheckedItemsStringGrid.RowCount + 1;
       CheckedItemsStringGrid.Cells[0, CheckedItemsStringGrid.RowCount-1] := BarcodeEdit.Text;
       CheckedItemsStringGrid.Cells[1, CheckedItemsStringGrid.RowCount-1] := ItemNameLabel.Caption;
       CheckedItemsStringGrid.Cells[2, CheckedItemsStringGrid.RowCount-1] := FormatDateTime('dd/mm/yyyy', Date);
       CheckedItemsStringGrid.Cells[3, CheckedItemsStringGrid.RowCount-1] := IntToStr(CountSpinEdit.Value);
       ClearButtonClick(nil);
       OKButton.Enabled := false;
     finally
       DB.Free;
     end;
  end;

  procedure UpdateItem(addtoexistingcount: Boolean);
  var
    r: Integer;
    DB: TSqlite3Dataset;
    query: TCaption;
  begin
    DB := TSqlite3Dataset.Create(nil);
    DB.FileName := SQLITEDATABASE;
     try
       if addtoexistingcount then DB.ExecSQL('UPDATE stocktake SET count=count+' + IntToStr(CountSpinEdit.Value)
                            + ', date=''' + FormatDateTime('yyyymmdd',Date) + ''''
                            + ' WHERE barcode=''' + BarcodeEdit.Text + '''')
                            else  DB.ExecSQL('UPDATE stocktake SET count=' + IntToStr(CountSpinEdit.Value)
                            + ', date=''' + FormatDateTime('yyyymmdd',Date) + ''''
                            + ' WHERE barcode=''' + BarcodeEdit.Text + '''');
       DB.SQL := 'SELECT count, date FROM stocktake'
                                 + ' WHERE barcode= ''' + BarcodeEdit.Text + '''';
       DB.Open;
       if DB.RecordCount>0 then
          begin
           r := 1;
           While r<CheckedItemsStringGrid.RowCount do
                 begin
                   if CheckedItemsStringGrid.Cells[0, r] = BarcodeEdit.Text then
                      begin
                        CheckedItemsStringGrid.Cells[3, r] := DB.FieldByName('count').AsString;
                        CheckedItemsStringGrid.Cells[2, r] := Copy(DB.FieldByName('date').AsString,7,2) + DateSeparator
                                                  + Copy(DB.FieldByName('date').AsString,5,2) + DateSeparator
                                                  + Copy(DB.FieldByName('date').AsString,1,4);
                        r := CheckedItemsStringGrid.RowCount;
                      end;
                   inc(r);
                 end;
          end;
     {  query := 'UPDATE products SET stocklevel=' + DB.FieldByName('count').AsString
                + ' WHERE barcode="' + BarcodeEdit.Text + '";';
       if not MainDB.ExecRemoteSQL( query, false) then
         begin
           writeln('Error sending stocklevel');
         end; }
     finally
       DB.Free;
     end;
  end;

begin
  if ItemNameLabel.Caption='' then
     begin
       ShowMessage('Error: invalid item code');
       Exit;
     end;
  if CountSpinEdit.Value >= 0 then
     if ItemAlreadyInStocktake
        then
          begin
            if ResolveExistingItemDlg.optionaddtoexisting.Checked then UpdateItem(true)
            else if ResolveExistingItemDlg.optionreplaceexisting.Checked then UpdateItem(false);
          end
        else StocktakeNewItem;
   ClearButtonClick(nil);
   OKButton.Enabled := false;
   BarcodeEdit.SetFocus;
   CheckedItemsStringGrid.Row := CheckedItemsStringGrid.RowCount-1;
end;

procedure TStocktakeForm.ClearButtonClick(Sender: TObject);
begin
  BarcodeEdit.Text:='';
  ItemNameLabel.Caption:='';
end;

procedure TStocktakeForm.CountSpinEditKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
  if Key=13 then
     begin
       OKButton.Setfocus;  //CountSpinEdit.Value := Round(CountSpinEdit.StrToValue(CountSpinEdit.Text));
       OKButton.Click; //BitBtn1Click(OKButton);
     end;
end;

function TStocktakeForm.GetBarcode(barcode: shortstring; var aProduct: TfpProduct
  ): integer;
var
  //DB: TSqlite3Dataset;
  r: integer;
begin
  Result := 0;
  MainDB.Close;
  MainDB.SQL := 'SELECT * FROM products'
                             + ' WHERE barcode= ''' + barcode + ''''
                             + ' ORDER BY barcode';
  MainDB.Open;
  if MainDB.RecordCount>0 then
     begin
       aProduct.barcode := barcode;
       aProduct.name := MainDB.FieldByName('name').AsString;
       aProduct.costprice := MainDB.FieldByName('costprice').AsFloat;
       aProduct.stocklevel := MainDB.FieldByName('stocklevel').AsInteger;
     end;
  Result := MainDB.RecordCount;
end;


initialization
  {$I stocktake.lrs}

end.

